package com.tansun.tandata.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description: POI处理excel工具类
 * @author: linLaiChun
 * @create: 2020-05-28
 * @Version v1.0
 **/
public class PoiExcelUtil {
    //标注起始
    public static final String POINT_START = "${";
    //标注结尾
    public static final String POINT_END= "}";

    /**
     * 根据列表模板导入数据
     * @param file
     * @param tempName
     * @return
     * @throws Exception
     */
    public static <T> List<List<T>> importByTemp(MultipartFile file,String tempName,Class<T> tClass) throws Exception {
        List<List<T>> resultList = new ArrayList<>();
        //获取模板
        Workbook tempWorkbook = getTempWorkbookInPro(tempName);
        List<SheetEntity> sheetEntityList = readListTemp(tempWorkbook);
        //读取导入文件内容
        Workbook workbook = openWorkbook(file.getInputStream(),file.getOriginalFilename());
        //迭代sheet
        for(int sheetNo=Constants.CHAR_INT_ZERO;sheetNo<tempWorkbook.getNumberOfSheets();sheetNo++){
            SheetEntity sheetEntity = sheetEntityList.get(sheetNo);
            List<T> sheetList = readListSheet(workbook, sheetEntity,tClass);
            resultList.add(sheetList);
        }
        return resultList;
    }

    /**
     * 根据列表模板读取sheet数据
     * @param workbook
     * @param sheetEntity
     * @return
     * @throws Exception
     */
    public static <T> List<T> readListSheet(Workbook workbook,SheetEntity sheetEntity,Class<T> tClass) throws Exception {
        Sheet sheet = workbook.getSheetAt(sheetEntity.getIndex());
        List<T> sheetList = new ArrayList<>();
        //迭代row
        for(int rowNo=sheetEntity.getStartRow();rowNo<sheet.getPhysicalNumberOfRows();rowNo++){
            Row row = sheet.getRow(rowNo);
            Map<String,String>  rowMap = new HashMap<>();
            //迭代cell
            for(int cellNo=Constants.CHAR_INT_ZERO;cellNo<sheetEntity.getPointList().size();cellNo++){
                SheetEntity.CellEntity cellEntity = sheetEntity.getPointList().get(cellNo);
                Cell cell = row.getCell(cellNo);
                cell.setCellType(CellType.STRING);
                String value = cell.getStringCellValue();
                rowMap.put(cellEntity.getValue(),value);
            }
            sheetList.add(MapUtils.transMap(tClass,rowMap));
        }
        return sheetList;
    }

    public static void downTemp(String tempName,String downName,HttpServletResponse response) throws Exception {
        Workbook workbook = getTempWorkbookInPro(tempName);
        List<SheetEntity> sheetEntityList = readListTemp(workbook);
        for(int sheetNo=Constants.CHAR_INT_ZERO;sheetNo<workbook.getNumberOfSheets();sheetNo++){
            setTempEmpty(sheetEntityList.get(sheetNo));
        }
        //下载文件
        ExcelUtils.downloadFile(toStream(workbook),downName,response);
    }

    /**
     * 基于模板导出列表类型的excel，支持多sheet
     * @param tempName
     * @param downName
     * @param dataList
     * @throws Exception
     */
    public static void exportByTemp(String tempName, String downName, List<List<Object>> dataList, HttpServletResponse response) throws Exception {
        //获取模板
        Workbook workbook = buildByTemp(tempName,dataList);
        //下载文件
        ExcelUtils.downloadFile(toStream(workbook),downName,response);
    }

    /**
     * 基于模板导出列表类型的excel，只支持第一个sheet
     * @param tempName
     * @param downName
     * @param dataList
     * @throws Exception
     */
    public static void exportByTempSingle(String tempName, String downName, List<Object> dataList,HttpServletResponse response) throws Exception {
        //获取模板
       Workbook workbook = buildByTempSingle(tempName,dataList);
        //下载文件
        ExcelUtils.downloadFile(toStream(workbook),downName,response);
    }

    /**
     * 使用模板构建列表类型的excel，支持多sheet
     * @param tempName
     * @param dataList
     * @return
     * @throws Exception
     */
    public static Workbook buildByTemp(String tempName, List<List<Object>> dataList) throws Exception {
        //获取模板
        Workbook workbook = getTempWorkbookInPro(tempName);
        List<SheetEntity> sheetEntityList = readListTemp(workbook);
        for(int sheetNo=Constants.CHAR_INT_ZERO;sheetNo<workbook.getNumberOfSheets();sheetNo++){
            setListSheet(workbook,sheetEntityList.get(sheetNo),dataList.get(sheetNo));
        }
        return workbook;
    }

    /**
     * 使用模板构建列表类型的excel，只支持第一个sheet
     * @param tempName
     * @param dataList
     * @return
     * @throws Exception
     */
	public static Workbook buildByTempSingle(String tempName, List<Object> dataList) throws Exception {
		Workbook workbook = getTempWorkbookInPro(tempName);
		List<SheetEntity> sheetEntityList = readListTemp(workbook);
        setListSheet(workbook,sheetEntityList.get(Constants.CHAR_INT_ZERO),dataList);
		return workbook;
	}

    /**
     * 将欲导出的数据写入列表模板
     * @param workbook
     * @param sheetEntity
     * @param datas
     */
    public static void setListSheet(Workbook workbook,SheetEntity sheetEntity,List<Object> datas) throws Exception {
        Sheet sheet = workbook.getSheetAt(sheetEntity.getIndex());
        List<SheetEntity.CellEntity> pointList = sheetEntity.getPointList();
        for(int rowNo = Constants.CHAR_INT_ZERO;rowNo < datas.size();rowNo++){
            Object data = datas.get(rowNo);
            Row row = sheet.createRow(rowNo + sheetEntity.getStartRow());
            row.setHeight(sheetEntity.getStartRowHeigh());
            for(int cellNo = Constants.CHAR_INT_ZERO;cellNo < pointList.size();cellNo++){
                SheetEntity.CellEntity cellEntity = pointList.get(cellNo);
                Cell cell = row.createCell(cellEntity.getCellIndex());
                cell.setCellStyle(cellEntity.getCellStyle());
                Object value = ClassUtils.getValueOfGetIncludeObjectFeild(data,cellEntity.getValue());
                cell.setCellValue(value==null?Constants.CHAR_BLANK:value.toString());
            }
        }
    }

    /**
     * 清空模板中的标注
     * @param sheetEntity
     * @throws Exception
     */
    public static void setTempEmpty(SheetEntity sheetEntity) throws Exception {
        Map<String, Cell> pointCellMap = sheetEntity.getPointCellMap();
        for(Cell cell:pointCellMap.values()){
            cell.setCellValue(Constants.CHAR_BLANK);
        }
    }

    /**
     * 读取列表模板结构信息
     * @param workbook
     * @return
     */
    public static List<SheetEntity> readListTemp(Workbook workbook){
        List<SheetEntity> sheetEntityList = new ArrayList<>();
        for (int sheetNo = Constants.CHAR_INT_ZERO; sheetNo < workbook.getNumberOfSheets(); sheetNo++) {
            Sheet sheet = workbook.getSheetAt(sheetNo);
            SheetEntity sheetEntity = new SheetEntity();
            sheetEntity.setName(sheet.getSheetName());
            sheetEntity.setIndex(sheetNo);
            Map<String,Cell> pointCellMap = new HashMap<>();
            List<SheetEntity.CellEntity> pointList = new ArrayList<>();
            for (int rowNo = Constants.CHAR_INT_ZERO; rowNo < sheet.getPhysicalNumberOfRows(); rowNo++) {
               Row row = sheet.getRow(rowNo);
                for (int cellNo = Constants.CHAR_INT_ZERO; cellNo < row.getPhysicalNumberOfCells(); cellNo++) {
                    Cell cell = row.getCell(cellNo);
                    String pointName = getPoint(cell.getStringCellValue());
                    if(pointName != null){
                        pointCellMap.put(pointName,cell);
                        SheetEntity.CellEntity cellEntity = new SheetEntity.CellEntity();
                        cellEntity.setValue(pointName);
                        cellEntity.setCellIndex(cellNo);
                        cellEntity.setCellStyle(cell.getCellStyle());
                        pointList.add(cellEntity);
                        if(sheetEntity.getStartRow()==null){
                            sheetEntity.setStartRow(rowNo);
                            sheetEntity.setStartRowHeigh(row.getHeight());
                        }
                    }
                }
            }
            sheetEntity.setPointCellMap(pointCellMap);
            sheetEntity.setPointList(pointList);
            sheetEntityList.add(sheetEntity);
        }
        return sheetEntityList;
    }

    /**
     * 从项目路径下获取模板信息
     * @param tempFilePath
     * @return
     */
    private static Workbook getTempWorkbookInPro(String tempFilePath){
        InputStream is = null;
        try {
            ClassPathResource cpr = new ClassPathResource("/templates/"+tempFilePath);
            is = cpr.getInputStream();
            return openWorkbook(is,tempFilePath);
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        } finally {
            IOUtils.closeQuietly(is);
        }
    }

    /**
     * 获取锚点信息
     * @param cellValue
     * @return
     */
    private static String getPoint(String cellValue){
        String pointName = null;
        if(cellValue != null){
            if(cellValue.startsWith(POINT_START) && cellValue.endsWith(POINT_END)){
                pointName = cellValue.replace(POINT_START,Constants.CHAR_BLANK).replace(POINT_END,Constants.CHAR_BLANK);
            }
        }
        return pointName;
    }


    /**
     * 模板转换成流
     * @param workbook
     * @return
     */
    public static InputStream toStream(Workbook workbook){
        ByteArrayInputStream bis = null;
        ByteArrayOutputStream bos = null;
        try {
            bos = new ByteArrayOutputStream();
            workbook.write(bos);
            bis = new ByteArrayInputStream(bos.toByteArray());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(bos);
            IOUtils.closeQuietly(bis);
        }
        return bis;
    }

    private static Workbook openWorkbook(InputStream in, String filename)
            throws IOException {
        Workbook wb = null;
        try {
            if (filename.endsWith(Constants.EXCEL_2007_UP)) {
                wb = new XSSFWorkbook(in);// Excel 2007
            } else {
                wb = new HSSFWorkbook(in);// Excel 2003
            }
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            IOUtils.closeQuietly(in);
        }
        return wb;
    }

}
